/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * PnlCourses.java
 *
 * Created on May 13, 2010, 6:07:13 PM
 */

package org.foment.gem.beans;

import java.awt.event.InputEvent;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.DefaultListModel;
import org.foment.joliage3.newt.KeyValue;
import org.foment.joliage3.sql.Database;

/**
 *
 * @author Dusan
 */
public class PnlCourses extends javax.swing.JPanel {

    /** Creates new form PnlCourses */
    public PnlCourses() {
        initComponents();
        setComponents();
    }

    private void setComponents()
    {
        comboDepartment.setQuery("SELECT departmentID as keyName, departmentTitle as fieldName from department");
        comboDepartment.listenTables("department");

        comboScienceDomain.setQuery("SELECT sd.scienceDomainID AS keyName, sd.scienceDomainTitle AS fieldName FROM scienceDomain sd " +
                " WHERE sd.scienceDomainID IN (SELECT scienceDomainID FROM scienceDomainCourses WHERE departmentID = " + comboDepartment.getValue() + " ) ; ");
        comboScienceDomain.insertItemAt(new KeyValue(-100, "Sve"), 0);
        comboScienceDomain.listenTables("scienceDomain, sciencedomaincourses");
        

        comboCourse.setQuery("SELECT courseID as keyName, courseTitle as fieldName FROM course WHERE " +
                " courseID IN ( SELECT courseID FROM scienceDomainCourses WHERE departmentID = " + comboDepartment.getValue() +
                " AND scienceDomainID = " + comboScienceDomain.getValue() + " ) ; ");
        comboCourse.insertItemAt(new KeyValue(-100, "Sve"), 0);
        comboCourse.listenTables("course, sciencedomaincourses");
    }
    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jScrollPane1 = new javax.swing.JScrollPane();
        listCourses = new org.foment.joliage3.newt.SQLListBox();
        comboDepartment = new org.foment.joliage3.newt.SQLComboBox();
        comboScienceDomain = new org.foment.joliage3.newt.SQLComboBox();
        comboCourse = new org.foment.joliage3.newt.SQLComboBox();
        btnAdd = new javax.swing.JButton();
        btnRemove = new javax.swing.JButton();

        jScrollPane1.setViewportView(listCourses);

        comboDepartment.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Item 1", "Item 2", "Item 3", "Item 4" }));
        comboDepartment.setMinimumSize(new java.awt.Dimension(25, 20));
        comboDepartment.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                comboDepartmentActionPerformed(evt);
            }
        });

        comboScienceDomain.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Item 1", "Item 2", "Item 3", "Item 4" }));
        comboScienceDomain.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                comboScienceDomainActionPerformed(evt);
            }
        });

        comboCourse.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Item 1", "Item 2", "Item 3", "Item 4" }));

        btnAdd.setText("+");
        btnAdd.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnAddActionPerformed(evt);
            }
        });

        btnRemove.setText("-");
        btnRemove.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                btnRemoveActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(comboDepartment, javax.swing.GroupLayout.DEFAULT_SIZE, 196, Short.MAX_VALUE)
                    .addComponent(comboScienceDomain, javax.swing.GroupLayout.DEFAULT_SIZE, 196, Short.MAX_VALUE)
                    .addComponent(comboCourse, javax.swing.GroupLayout.DEFAULT_SIZE, 196, Short.MAX_VALUE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(btnRemove)
                    .addComponent(btnAdd))
                .addGap(29, 29, 29)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 236, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(52, 52, 52))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(20, 20, 20)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(comboDepartment, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(btnAdd))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(comboScienceDomain, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(btnRemove))
                        .addGap(11, 11, 11)
                        .addComponent(comboCourse, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap(24, Short.MAX_VALUE))
        );
    }// </editor-fold>//GEN-END:initComponents

    private void comboDepartmentActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_comboDepartmentActionPerformed
        // TODO add your handling code here:
        comboScienceDomain.setQuery("SELECT sd.scienceDomainID AS keyName, sd.scienceDomainTitle AS fieldName FROM scienceDomain sd " +
                " WHERE sd.scienceDomainID IN (SELECT scienceDomainID FROM scienceDomainCourses WHERE departmentID = " + comboDepartment.getValue() + " ) ; " );
        comboScienceDomain.insertItemAt(new KeyValue(-100, "Sve"), 0);
        comboCourse.setQuery("SELECT courseID as keyName, courseTitle as fieldName FROM course WHERE " +
                " courseID IN ( SELECT courseID FROM scienceDomainCourses WHERE departmentID = " + comboDepartment.getValue() +
                " AND scienceDomainID = " + comboScienceDomain.getValue() + " ) ; " );
        comboCourse.insertItemAt(new KeyValue(-100, "Sve"), 0);
    }//GEN-LAST:event_comboDepartmentActionPerformed

    private void comboScienceDomainActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_comboScienceDomainActionPerformed
        // TODO add your handling code here:
        comboCourse.setQuery("SELECT courseID as keyName, courseTitle as fieldName FROM course WHERE " +
                " courseID IN ( SELECT courseID FROM scienceDomainCourses WHERE departmentID = " + comboDepartment.getValue() +
                " AND scienceDomainID = " + comboScienceDomain.getValue() + " ) ; " );
        comboCourse.insertItemAt(new KeyValue(-100, "Sve"), 0);
    }//GEN-LAST:event_comboScienceDomainActionPerformed

    private void btnAddActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnAddActionPerformed
        // TODO add your handling code here:
        // ako smo izabrali SVE naucne oblasti za odredjenu katedru
        KeyValue kv = (KeyValue)comboScienceDomain.getSelectedItem();
        if (kv.getKey() == -100)
        {
            String query = "SELECT c.courseID, c.courseTitle FROM course c, sciencedomaincourses sdc WHERE " +
                    " sdc.courseID = c.courseID AND departmentID = '" + comboDepartment.getValue() + "' ;";
            try
            {
                Connection  conn = Database.getConnection();
                rs = conn.createStatement().executeQuery(query);
                while (rs.next())
                {
                    courses.addElement(new KeyValue(rs.getInt("courseID"), rs.getString("courseTitle")));
                }
                rs.close();
                conn.close();
            }
            catch(SQLException e) {e.printStackTrace();}
        }
        else
        {
            kv = ((KeyValue)comboCourse.getSelectedItem());
            // sad gledamo da li smo izabrali mozda SVE predmete za naucnu oblast
            if (kv.getKey() == -100)
            {
                for (int i = 0; i < comboCourse.getItemCount(); i++)
                    if (comboCourse.getItemAt(i) == kv)
                    continue;
                else
                    courses.addElement(comboCourse.getItemAt(i));
            }
            else
                courses.addElement(comboCourse.getSelectedItem());
        }
        listCourses.setModel(courses);
    }//GEN-LAST:event_btnAddActionPerformed

    private void btnRemoveActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnRemoveActionPerformed
        // TODO add your handling code here:
        courses.removeElement(listCourses.getSelectedValue());
        listCourses.setModel(courses);
    }//GEN-LAST:event_btnRemoveActionPerformed

    public String getQuery()
    {
        // ako nista nema u listi, tada ne kreiramo upit
        if (courses.size() == 0)
            return "";

        String query = "";
        query += " EmployeeCourse.courseID IN ( ";
        int size = courses.size();
        for (int i = 0; i < size-1; i++)
            query = query + " '" + ((KeyValue)(courses.get(i))).getKey() + "' , ";
        query = query + " '" + ((KeyValue)(courses.lastElement())).getKey() + "' )";

        return query;
    }

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton btnAdd;
    private javax.swing.JButton btnRemove;
    private org.foment.joliage3.newt.SQLComboBox comboCourse;
    private org.foment.joliage3.newt.SQLComboBox comboDepartment;
    private org.foment.joliage3.newt.SQLComboBox comboScienceDomain;
    private javax.swing.JScrollPane jScrollPane1;
    private org.foment.joliage3.newt.SQLListBox listCourses;
    // End of variables declaration//GEN-END:variables

    private ResultSet rs;
    DefaultListModel courses = new DefaultListModel();
}
